package org.light.utils;
import java.util.Iterator;
import java.util.Set;

import org.light.domain.Domain;
import org.light.domain.Field;
import org.light.domain.Var;

public class MybatisSqlReflector {
	public static String generateTableDefinition(Domain domain) throws Exception{
		String result = "create table " + domain.getDbPrefix() + TableStringUtil.domainNametoTableName(domain) +" (";
		Iterator it = domain.getFields().iterator();
        while (it.hasNext()) {	
	        Field f = (Field)it.next();
	        String fieldName = f.getFieldName();
	        String fieldType = f.getFieldType();
	        result += changeDomainFieldtoTableColumDefinitionToken(domain, fieldName, fieldType)+ ",";
        }
		String  ptoken = generatePrimaryKeySqlToken(domain);
		if (ptoken.length() >0 ){
			result += ptoken;
		}else {
			result = result.substring(0,result.length()-1);
		}
        result += ")";
		return result;
	}

	public static String generateInsertSql(Domain domain) throws Exception{
		String result = "insert into " + domain.getDbPrefix() +TableStringUtil.domainNametoTableName(domain) +" ";
		Iterator it = domain.getFieldsWithoutId().iterator();
		
		result += "( ";
        while (it.hasNext()) {	
        	Field f = (Field)it.next();
 	        String fieldName = f.getFieldName();
 	        String fieldType = f.getFieldType();
	        result += StringUtil.changeDomainFieldtoTableColum(fieldName)+ ",";
        }
        result = result.substring(0,result.length()-1);
        result += ") values (";
		Iterator it2 = domain.getFieldsWithoutId().iterator();
        while (it2.hasNext()) {	
        	Field f = (Field)it2.next();
 	        String fieldName = f.getFieldName();
 	        String fieldType = f.getFieldType();
	        result += "#{"+fieldName+"},";
        }
        result = result.substring(0,result.length()-1);
        result += ")";
        return result;
	}
	
	public static String generateInsertSqlWithDeniedFields(Domain domain,Set<Field> deniedFields) throws Exception{
		String result = "insert into " + domain.getDbPrefix() +TableStringUtil.domainNametoTableName(domain) +" ";
		Iterator it = domain.getFieldsWithoutId().iterator();
		
		result += "( ";
        while (it.hasNext()) {	
        	Field f = (Field)it.next();
 	        String fieldName = f.getFieldName();
 	        String fieldType = f.getFieldType();
 	        if (!deniedFields.contains(f)) {
 	        	result += StringUtil.changeDomainFieldtoTableColum(fieldName)+ ",";
 	        }
        }
        result = result.substring(0,result.length()-1);
        result += ") values (";
		Iterator it2 = domain.getFieldsWithoutId().iterator();
        while (it2.hasNext()) {	
        	Field f = (Field)it2.next();
 	        String fieldName = f.getFieldName();
 	        String fieldType = f.getFieldType();
 	        if (!deniedFields.contains(f)) {
 	    	   result += "#{"+fieldName+"},";
 	       }
        }
        result = result.substring(0,result.length()-1);
        result += ")";
        return result;
	}
	
	public static String generateInsertLinkTwoSql(Domain master,Domain slave) throws Exception{
			String result = "insert into " +TableStringUtil.twoDomainNametoTableNameWithDbPrefix(master, slave) +" ";	
			result += "( ";
			result += StringUtil.changeDomainFieldtoTableColum(master.getCapFirstDomainName()+"Id");
			result += ",";
			if (StringUtil.isBlank(slave.getAlias())) {
				result += StringUtil.changeDomainFieldtoTableColum(slave.getCapFirstDomainName()+"Id");
			}else {
				result += StringUtil.changeDomainFieldtoTableColum(StringUtil.capFirst(slave.getAlias())+"Id");
			}
	        result += ") values (#{"+master.getLowerFirstDomainName()+"Id},#{"+StringUtil.lowerFirst(slave.getAlias())+"Id})";
	        return result;
		
	}
	
	public static String generateDeleteLinkTwoSql(Domain master,Domain slave) throws Exception{
		String result = "delete from " +TableStringUtil.twoDomainNametoTableNameWithDbPrefix(master, slave) +" ";	
		result += " where ";
		result += StringUtil.changeDomainFieldtoTableColum(master.getCapFirstDomainName()+"Id");
		result += " = #{"+master.getLowerFirstDomainName()+"Id} and ";
		if (StringUtil.isBlank(slave.getAlias())){
			result += StringUtil.changeDomainFieldtoTableColum(slave.getCapFirstDomainName()+"Id");
		} else {
			result += StringUtil.changeDomainFieldtoTableColum(StringUtil.capFirst(slave.getAlias())+"Id");
		}
        result += " = #{"+StringUtil.lowerFirst(slave.getAlias())+"Id}";
        return result;
	}
	
	public static String generateUpdateSql(Domain domain) throws Exception{
		String result = "update " +domain.getDbPrefix() + TableStringUtil.domainNametoTableName(domain) +" set ";
		Iterator it = domain.getFieldsWithoutId().iterator();
        while (it.hasNext()) {	
        	Field f = (Field)it.next();
 	        String fieldName = f.getFieldName();
 	        String fieldType = f.getFieldType();
	        if (!isPrimaryKey(domain, fieldName, fieldType)) {
	        	result += StringUtil.changeDomainFieldtoTableColum(fieldName)+ " = #{"+fieldName+"} ,";
	        }
        }
        result = result.substring(0,result.length()-1);
        result += generatePrimaryWhereParamSqlTokenWithDomainId(domain);
        return result;
	}
	
	public static String generateUpdateSqlWithDeniedFields(Domain domain,Set<Field> deniedFields) throws Exception{
		String result = "update " +domain.getDbPrefix() + TableStringUtil.domainNametoTableName(domain) +" set ";
		Iterator it = domain.getFieldsWithoutId().iterator();
        while (it.hasNext()) {	
        	Field f = (Field)it.next();
        	if (!deniedFields.contains(f)) {
	 	        String fieldName = f.getFieldName();
	 	        String fieldType = f.getFieldType();
		        if (!isPrimaryKey(domain, fieldName, fieldType)) {
		        	result += StringUtil.changeDomainFieldtoTableColum(fieldName)+ " = #{"+fieldName+"} ,";
		        }
        	}
        }
        result = result.substring(0,result.length()-1);
        result += generatePrimaryWhereParamSqlTokenWithDomainId(domain);
        return result;
	}
	
	public static String generateUpdateSqlWithDenies(Domain domain,Set<String> deniedFieldNames) throws Exception{
		String result = "update " +domain.getDbPrefix() + TableStringUtil.domainNametoTableName(domain) +" set ";
		Iterator it = domain.getFieldsWithoutId().iterator();
        while (it.hasNext()) {	
        	Field f = (Field)it.next();        	
 	        String fieldName = f.getFieldName();
 	        String fieldType = f.getFieldType();
 	        if (!deniedFieldNames.contains(fieldName)) {
		        if (!isPrimaryKey(domain, fieldName, fieldType)) {
		        	result += StringUtil.changeDomainFieldtoTableColum(fieldName)+ " = #{"+fieldName+"} ,";
		        }
	        }
        }
        result = result.substring(0,result.length()-1);
        result += generatePrimaryWhereParamSqlTokenWithDomainId(domain);
        return result;
	}
	
	public static String generateDeleteSqlWithValue(Domain domain) throws Exception{
		String result = "delete from " +domain.getDbPrefix() + TableStringUtil.domainNametoTableName(domain) + " ";
        result += generatePrimaryWhereParamSqlTokenWithDomainId(domain);
        return result;
	}

	public static String generateSoftDeleteSqlWithValue(Domain domain) throws Exception{
		String result = "update " +domain.getDbPrefix() + TableStringUtil.domainNametoTableName(domain) + " set " + StringUtil.changeDomainFieldtoTableColum(domain.getActive().getFieldName()) + " = "+domain.getDomainDeletedStr()+" where ";
		result += StringUtil.changeDomainFieldtoTableColum(domain.getDomainId().getFieldName())+ " = #{value} ";
        return result;
	}
	
	public static String generateActivateSqlWithValue(Domain domain) throws Exception{
		String result = "update " +domain.getDbPrefix() + TableStringUtil.domainNametoTableName(domain) + " set " + StringUtil.changeDomainFieldtoTableColum(domain.getActive().getFieldName()) + " = "+domain.getDomainActiveStr()+" where ";
		result += StringUtil.changeDomainFieldtoTableColum(domain.getDomainId().getFieldName())+ " = #{value} ";
        return result;
	}
	
	public static String changeDomainFieldtoTableColumDefinitionToken(Domain domain, String fieldName, String fieldType){
		String result = StringUtil.changeDomainFieldtoTableColum(fieldName) + " ";
		result += lookupSqlType(fieldName,fieldType) + " ";
		if (isPrimaryKey(domain,fieldName,fieldType)){
			result += "not null auto_increment";
		} else {
			result += "null ";
		}
		return result;
	}
	
	public static boolean isPrimaryKey(Domain domain,String fieldName,String fieldType){
		boolean retVal = false;
		if ("long".equalsIgnoreCase(fieldType)||"bigint".equalsIgnoreCase(fieldType)||"int".equalsIgnoreCase(fieldType)||"Integer".equalsIgnoreCase(fieldType)){
			if ("id".equalsIgnoreCase(fieldName)||fieldName.equalsIgnoreCase(domain.getStandardName()+"id")||fieldName.equalsIgnoreCase(domain.getDomainId().getFieldName())){
				retVal =true;
			}
		}
		return retVal;
	}
	
	public static String generatePrimaryKeySqlToken(Domain domain){
		String result = "";
		Iterator it = domain.getFields().iterator();
        while (it.hasNext()) {	
        	Field f = (Field)it.next();
 	        String fieldName = f.getFieldName();
 	        String fieldType = f.getFieldType();
	        if (isPrimaryKey(domain, fieldName, fieldType)){
	        	result =  "primary key (" + StringUtil.changeDomainFieldtoTableColum(fieldName) +")";
	        }
        }
        return result;
	}
	
	public static String generatePrimaryWhereParamSqlTokenWithDomainId(Domain domain){
		String result = "";
		Iterator it = domain.getFields().iterator();
        while (it.hasNext()) {	
        	Field f = (Field)it.next();
 	        String fieldName = f.getFieldName();
 	        String fieldType = f.getFieldType();
	        if (isPrimaryKey(domain, fieldName, fieldType)){
	        	result = "where " +  StringUtil.changeDomainFieldtoTableColum(fieldName) +" = #{"+domain.getDomainId().getLowerFirstFieldName()+"}";
	        }
        }
        return result;
	}
	
	public static String lookupSqlType(String fieldName, String fieldType){
		String result = "";
		if (fieldType.equalsIgnoreCase("long")) {
			result = "BigInt";
		}
		if  (fieldType.equalsIgnoreCase("int")||fieldType.equalsIgnoreCase("int")) {
			result = "Integer";
		}
		if  (fieldType.equalsIgnoreCase("float")||fieldType.equalsIgnoreCase("double")) {
			result = "Double";
		}
		if  (fieldType.equalsIgnoreCase("BigDecimal")||fieldType.equalsIgnoreCase("decimal")) {
			result = "Decimal";
		}
		if  (fieldType.equalsIgnoreCase("boolean")) {
			result = "bool";
		}
		if  (fieldType.equalsIgnoreCase("String")) {
			if (fieldName.toLowerCase().contains("comment")||fieldName.toLowerCase().contains("description")||fieldName.toLowerCase().contains("content")){
				result = "text";
			}else {
				result = "varchar(256)";
			}
		}
		return result; 
	}
	
	public static String generateSelectAllStatement(Domain domain) throws Exception{
		String result = "select "+ DomainTokenUtil.generateTableCommaFields(domain) + " from "+ domain.getDbPrefix() +TableStringUtil.domainNametoTableName(domain);
        return result;
	}
	
	public static String generateSelectAllStatementWithDeniedFields(Domain domain,Set<Field> deniedFields) throws Exception{
		String result = "select "+ DomainTokenUtil.generateTableCommaFieldsWithDeniedFields(domain,deniedFields) + " from "+ domain.getDbPrefix() +TableStringUtil.domainNametoTableName(domain);
        return result;
	}

	public static String generateSelectAllByLimitStatement(Domain domain) throws Exception{
		String result = "select "+ DomainTokenUtil.generateTableCommaFields(domain) + " from "+ domain.getDbPrefix() +TableStringUtil.domainNametoTableName(domain) +" limit #{limit} offset #{start}";
        return result;
	}
	
	static String generateSelectAllByLimitStatement(Domain domain, Var start, Var length) throws Exception{
		String result = "select "+ DomainTokenUtil.generateTableCommaFields(domain) + " from "+ domain.getDbPrefix() +TableStringUtil.domainNametoTableName(domain) +" limit #{"+start.getVarName()+"},#{"+length.getVarName()+"};";
        return result;
	}
	
	public static String generateCountRecordStatement(Domain domain, Var countNum) throws Exception{
		String result = "select count("+ StringUtil.changeDomainFieldtoTableColum(domain.getDomainId().getFieldName()) + ") as "+countNum.getVarName()+" from "+ domain.getDbPrefix() +TableStringUtil.domainNametoTableName(domain) +";";
        return result;
	}
	
	public static String generateSelectByFieldStatement(Domain domain, Field field) throws Exception{
		String result = "select "+  DomainTokenUtil.generateTableCommaFields(domain) + " from "+ domain.getDbPrefix() +TableStringUtil.domainNametoTableName(domain) +" where "+ StringUtil.changeDomainFieldtoTableColum(field.getFieldName())+" = #{"+field.getFieldName()+"};";
        return result;
	}
	
	public static String generateSelectActiveStatement(Domain domain) throws Exception{
		String result = "select "+ DomainTokenUtil.generateTableCommaFields(domain) + " from "+ domain.getDbPrefix() +TableStringUtil.domainNametoTableName(domain) +" where "+StringUtil.changeDomainFieldtoTableColum(domain.getActive().getFieldName())+" = "+domain.getDomainActiveStr();
		if (domain.getDomainName()!=null) result += " order by " + StringUtil.changeDomainFieldtoTableColum(domain.getDomainName().getFieldName()) + " asc";
		return result;
	}
	
	public static String generateSelectActiveStatementWithDeniedFields(Domain domain,Set<Field> deniedFields) throws Exception{
		String result = "select "+ DomainTokenUtil.generateTableCommaFieldsWithDeniedFields(domain,deniedFields) + " from "+ domain.getDbPrefix() +TableStringUtil.domainNametoTableName(domain) +" where "+StringUtil.changeDomainFieldtoTableColum(domain.getActive().getFieldName())+" = "+domain.getDomainActiveStr();
		if (domain.getDomainName()!=null) result += " order by " + StringUtil.changeDomainFieldtoTableColum(domain.getDomainName().getFieldName()) + " asc";
		return result;
	}
	
	public static String generateFindByIdStatement(Domain domain) throws Exception{
		String result = "select "+DomainTokenUtil.generateTableCommaFields(domain) + " from "+ domain.getDbPrefix() + TableStringUtil.domainNametoTableName(domain) +" where "+StringUtil.changeDomainFieldtoTableColum(domain.getDomainId().getFieldName())+" = #{"+domain.getDomainId().getFieldName()+"}";
        return result;
	}
	
	public static String generateFindByIdStatementWithDeniedFields(Domain domain,Set<Field> deniedFields) throws Exception{
		String result = "select "+DomainTokenUtil.generateTableCommaFieldsWithDeniedFields(domain,deniedFields) + " from "+ domain.getDbPrefix() + TableStringUtil.domainNametoTableName(domain) +" where "+StringUtil.changeDomainFieldtoTableColum(domain.getDomainId().getFieldName())+" = #{"+domain.getDomainId().getFieldName()+"}";
        return result;
	}
	
	public static String generateFindByNameStatement(Domain domain) throws Exception{
		String result = "select "+ DomainTokenUtil.generateTableCommaFields(domain) + " from "+ domain.getDbPrefix() + TableStringUtil.domainNametoTableName(domain) +" where "+StringUtil.changeDomainFieldtoTableColum(domain.getDomainName().getFieldName())+" = #{"+domain.getDomainName().getFieldName()+"}";
        return result;
	}
	
	public static String generateFindByNameStatementWithDeniedFields(Domain domain,Set<Field> deniedFields) throws Exception{
		String result = "select "+ DomainTokenUtil.generateTableCommaFieldsWithDeniedFields(domain,deniedFields) + " from "+ domain.getDbPrefix() + TableStringUtil.domainNametoTableName(domain) +" where "+StringUtil.changeDomainFieldtoTableColum(domain.getDomainName().getFieldName())+" = #{"+domain.getDomainName().getFieldName()+"}";
        return result;
	}
	
	public static String generateSearchByNameStatement(Domain domain) throws Exception{
		String result = "select "+ DomainTokenUtil.generateTableCommaFields(domain) + " from "+ domain.getDbPrefix() + TableStringUtil.domainNametoTableName(domain) +" where "+StringUtil.changeDomainFieldtoTableColum(domain.getDomainName().getFieldName())+" like #{"+domain.getDomainName().getFieldName()+"}";
        return result;
	}
	
	public static String generateSearchByNameUsingValueStatement(Domain domain) throws Exception{
		String result = "select "+ DomainTokenUtil.generateTableCommaFields(domain) + " from "+ domain.getDbPrefix() + TableStringUtil.domainNametoTableName(domain) +" where "+StringUtil.changeDomainFieldtoTableColum(domain.getDomainName().getFieldName())+" like CONCAT(CONCAT('%',#{value}),'%')";
        return result;
	}
	
	public static String generateSearchByNameUsingValueStatementWithDeniedFields(Domain domain,Set<Field> deniedFields) throws Exception{
		String result = "select "+ DomainTokenUtil.generateTableCommaFieldsWithDeniedFields(domain,deniedFields) + " from "+ domain.getDbPrefix() + TableStringUtil.domainNametoTableName(domain) +" where "+StringUtil.changeDomainFieldtoTableColum(domain.getDomainName().getFieldName())+" like CONCAT(CONCAT('%',#{value}),'%')";
        return result;
	}
	
	public static String generateSearchByDescriptionStatement(Domain domain, Field description) throws Exception{
		String result = "select " +DomainTokenUtil.generateTableCommaFields(domain) + " from "+ domain.getDbPrefix() + TableStringUtil.domainNametoTableName(domain) +" where "+StringUtil.changeDomainFieldtoTableColum(description.getFieldName())+" like "+StringUtil.changeDomainFieldtoTableColum(description.getFieldName())+";";
        return result;
	}
	
	public static String generateToggleSqlWithValue(Domain domain) throws Exception{
		String result = "update " +domain.getDbPrefix() + TableStringUtil.domainNametoTableName(domain) + " set " + StringUtil.changeDomainFieldtoTableColum(domain.getActive().getFieldName()) + " = not "+ StringUtil.changeDomainFieldtoTableColum(domain.getActive().getFieldName())+" where ";
		result += StringUtil.changeDomainFieldtoTableColum(domain.getDomainId().getFieldName())+ " = #{value} ";
        return result;
	}
	
	public static String generateSelectActiveUsingMasterIdStatement(Domain master,Domain slave) throws Exception{
		String result = "";
		if (StringUtil.isBlank(slave.getAlias())) {
			result = "select distinct "+ DomainTokenUtil.generateTableCommaFieldsWithTablePrefix(slave) + " from "+ TableStringUtil.domainNametoTableNameWithDbPrefix(slave)+ ","+TableStringUtil.twoDomainNametoTableNameWithDbPrefix(master, slave)+" where " + TableStringUtil.domainNametoTableNameWithDbPrefix(slave) +"."+slave.getDomainId().getFeildNameAsTableColumn()+" = "+TableStringUtil.twoDomainNametoTableNameWithDbPrefix(master, slave)+"."+StringUtil.changeDomainFieldtoTableColum(slave.getCapFirstDomainName()+"Id")+
						" and "+TableStringUtil.twoDomainNametoTableNameWithDbPrefix(master, slave) +"."+StringUtil.changeDomainFieldtoTableColum(master.getCapFirstDomainName()+"Id") +" = #{value} and "+TableStringUtil.domainNametoTableNameWithDbPrefix(slave) + "." + slave.getActive().getFeildNameAsTableColumn() + " = "+slave.getDomainActiveStr();
		}else {
			result = "select distinct "+ DomainTokenUtil.generateTableCommaFieldsWithTablePrefix(slave) + " from "+ TableStringUtil.domainNametoTableNameWithDbPrefix(slave)+ ","+TableStringUtil.twoDomainNametoTableNameWithDbPrefix(master, slave)+" where " + TableStringUtil.domainNametoTableNameWithDbPrefix(slave) +"."+slave.getDomainId().getFeildNameAsTableColumn()+" = "+TableStringUtil.twoDomainNametoTableNameWithDbPrefix(master, slave)+"."+StringUtil.changeDomainFieldtoTableColum(StringUtil.capFirst(slave.getAlias())+"Id")+
					" and "+TableStringUtil.twoDomainNametoTableNameWithDbPrefix(master, slave) +"."+StringUtil.changeDomainFieldtoTableColum(master.getCapFirstDomainName()+"Id") +" = #{value} and "+TableStringUtil.domainNametoTableNameWithDbPrefix(slave) + "." + slave.getActive().getFeildNameAsTableColumn() + " = "+slave.getDomainActiveStr();
		}
		if (slave.getDomainName()!=null) result += " order by "+  slave.getDbPrefix() + TableStringUtil.domainNametoTableName(slave) +"."+StringUtil.changeDomainFieldtoTableColum(slave.getDomainName().getFieldName())+" asc ";
		return result;
	}
}
